OpenEdge Development:<br />Progress 4GL Handbook Using calculated columns

A browse can show calculated results involving other browse columns and other available data values as separate columns in the browse. You accomplish this by adding the expression for each calculated column to the DEFINE BROWSE statement. All valid browse format phrase options are legal extensions to the expression (for example, a LABEL option). When the browse is opened, the calculated columns are displayed for each row.

For an updateable browse, however, you need to refresh the calculation if one of the values in the expression changes. The browse displays the appropriate calculations only when the query refreshes the data. It is more appropriate to refresh the calculated data programmatically when the user finishes editing the affected row. To accomplish this, use a base field as a placeholder for the expression. You then can reference the calculation and refresh the result as needed.

To see an example, you can add a column to the Order browse in your test procedure to display the number of days between the OrderDate and the PromiseDate.

Open h-CustOrderWin4.w and save it as h-CustOrderWin5.w.

Define a variable in the Definitions section to act as the placeholder for the calculated field. Call this Integer variable iPromiseDays:


DEFINE VARIABLE iPromiseDays AS INTEGER NO-UNDO.

Define the calculation as a column in the browse that is effectively displayed at the placeholder variable. You do this by including the expression for the calculation in the DISPLAY list followed by the at-sign (@) followed by the name of the placeholder variable that is used to store the value and represent its display format. Give the calculated field a COLUMN-LABEL of “Promise!Days” to see the effect of creating a stacked label:

DEFINE BROWSE OrderBrowse QUERY OrderBrowse NO-LOCK DISPLAY Order.Ordernum FORMAT "zzzzzzzzz9":U WIDTH 10.2 Order.OrderDate FORMAT "99/99/99":U Order.PromiseDate FORMAT "99/99/99":U Order.ShipDate FORMAT "99/99/9999":U `Order.PromiseDate - Order.OrderDate @ iPromiseDays` COLUMN-LABEL "Promise!Days" Order.PO FORMAT "x(20)":U WIDTH 17.2 WITH NO-ROW-MARKERS SEPARATORS SIZE 65 BY 6.19 ROW-HEIGHT-CHARS .57 EXPANDABLE.

DEFINE BROWSE OrderBrowse 
  QUERY OrderBrowse NO-LOCK DISPLAY 
      Order.Ordernum FORMAT "zzzzzzzzz9":U WIDTH 10.2 
      Order.OrderDate FORMAT "99/99/99":U 
      Order.PromiseDate FORMAT "99/99/99":U 
      Order.ShipDate FORMAT "99/99/9999":U 
      Order.PromiseDate - Order.OrderDate @ iPromiseDays  
          COLUMN-LABEL "Promise!Days" 
      Order.PO FORMAT "x(20)":U WIDTH 17.2 
    WITH NO-ROW-MARKERS SEPARATORS SIZE 65 BY 6.19 ROW-HEIGHT-CHARS .57 
EXPANDABLE.

Run h-CustOrderWin5.w. You see the calculation along with the other columns (it just happens that the number of days is always 5 in the test data in the Sports2000 database):

Note: When there are calculated fields in the DISPLAY phrase, you cannot use the ENABLE ALL option unless you use the EXCEPT option to exclude the calculated field, since calculated fields cannot be enabled.